CREATE PROCEDURE sprocPS_CarInsuranceCostAnalysisSummaryReport
@ScenarioId int,
@ProviderCompanyId int,
@CarMakeId int,
@CarModelId int

Set @ScenarioId = 0
Set @ProviderCompanyId = 0
Set @CarMakeId = 0
Set @CarModelId = 0

--Select  from tblPremiumRankByCarMakeModel

Select * from tblPremiumRankByCompanyTemp
Select * from tblPremiumRankByCompanyTempReport

Update tblPremiumRankByCompanyTempReport Set FirstCount = 0, SecondCount = 0, ThirdCount = 0, FourthCount=0, FifthCount=0

Declare @strWhere varchar(200)
Set @strWhere = ''

IF @ScenarioId > 0
	BEGIN
		Set @strWhere = @strWhere + ' And ScenarioId = ' + Cast(@ScenarioId as varchar(10))
	END

IF @ProviderCompanyId > 0
	BEGIN
		Set @strWhere = @strWhere + ' And ProviderCompanyId = ' + Cast(@ProviderCompanyId as varchar(10))
	END

IF @CarMakeId > 0
	BEGIN
		Set @strWhere = @strWhere + ' And CarMakeId = ' + Cast(@CarMakeId as varchar(10))
	END

IF @CarMakeId > 0
	BEGIN
		Set @strWhere = @strWhere + ' And CarMakeId = ' + Cast(@CarMakeId as varchar(10))
	END


IF @CarModelId > 0
	BEGIN
		Set @strWhere = @strWhere + ' And CarModelId = ' + Cast(@CarModelId as varchar(10))
	END

Select @strWhere 
Select COUNT(*) from tblPremiumRankByCarMakeModel 
Declare @cPosition int
Declare @strSQL varchar(500)
Set @cPosition = 1
While (@cPosition < 6)
BEGIN
	Select 'Position' + Cast(@cPosition as varchar(1))
	Declare @FirstCount as int
	Declare @SecondCount as int
	Declare @ThirdCount as int
	Declare @FourthCount as int
	Declare @FifthCount as int
	
	IF @cPosition = 1
		BEGIN
			CREATE table #tblTemp1CompanyRank
			(ProviderCompanyId bigint, RankCount int)
			
			INSERT INTO #tblTemp1CompanyRank(RankCount, ProviderCompanyId) 
			EXEC ('Select COUNT(ProviderCompanyId), ProviderCompanyId FROM tblPremiumRankByCarMakeModel Where Position = 1' + @strWhere + 'Group By ProviderCompanyId ')
			
			--Select * from tblTempCompanyRank
			
			UPDATE    PR
				SET   FirstCount = T.RankCount
			FROM #tblTemp1CompanyRank AS T INNER JOIN
			tblPremiumRankByCompanyTempReport AS PR ON T.ProviderCompanyId = PR.ProviderCompanyId
								  
			--Select * from tblPremiumRankByCompanyTempReport
			
			DROP TABLE #tblTemp1CompanyRank
		END
	ELSE IF @cPosition = 2
		BEGIN
			CREATE table #tblTemp2CompanyRank
			(ProviderCompanyId bigint, RankCount int)
			
			INSERT INTO #tblTemp2CompanyRank(RankCount, ProviderCompanyId) 
			EXEC ('Select COUNT(ProviderCompanyId), ProviderCompanyId FROM tblPremiumRankByCarMakeModel Where Position = 2' + @strWhere + 'Group By ProviderCompanyId ')
			
			--Select * from tblTempCompanyRank
			
			UPDATE    PR
				SET   SecondCount = T.RankCount
			FROM #tblTemp2CompanyRank AS T INNER JOIN
			tblPremiumRankByCompanyTempReport AS PR ON T.ProviderCompanyId = PR.ProviderCompanyId
								  
			--Select * from tblPremiumRankByCompanyTempReport
			
			DROP TABLE #tblTemp2CompanyRank
		END
	ELSE IF @cPosition = 3
		BEGIN
			CREATE table #tblTemp3CompanyRank
			(ProviderCompanyId bigint, RankCount int)
			
			INSERT INTO #tblTemp3CompanyRank(RankCount, ProviderCompanyId) 
			EXEC ('Select COUNT(ProviderCompanyId), ProviderCompanyId FROM tblPremiumRankByCarMakeModel Where Position = 3' + @strWhere + 'Group By ProviderCompanyId ')
			
			--Select * from tblTempCompanyRank
			
			UPDATE    PR
				SET   ThirdCount = T.RankCount
			FROM #tblTemp3CompanyRank AS T INNER JOIN
			tblPremiumRankByCompanyTempReport AS PR ON T.ProviderCompanyId = PR.ProviderCompanyId
								  
			--Select * from tblPremiumRankByCompanyTempReport
			
			DROP TABLE #tblTemp3CompanyRank
		END
	ELSE IF @cPosition = 4
		BEGIN
			CREATE table #tblTemp4CompanyRank
			(ProviderCompanyId bigint, RankCount int)
			
			INSERT INTO #tblTemp4CompanyRank(RankCount, ProviderCompanyId) 
			EXEC ('Select COUNT(ProviderCompanyId), ProviderCompanyId FROM tblPremiumRankByCarMakeModel Where Position = 4' + @strWhere + 'Group By ProviderCompanyId ')
			
			--Select * from tblTempCompanyRank
			
			UPDATE    PR
				SET   FourthCount = T.RankCount
			FROM #tblTemp4CompanyRank AS T INNER JOIN
			tblPremiumRankByCompanyTempReport AS PR ON T.ProviderCompanyId = PR.ProviderCompanyId
								  
			--Select * from tblPremiumRankByCompanyTempReport
			
			DROP TABLE #tblTemp4CompanyRank
		END
	ELSE IF @cPosition = 5
		BEGIN
			CREATE table #tblTemp5CompanyRank
			(ProviderCompanyId bigint, RankCount int)
			
			INSERT INTO #tblTemp5CompanyRank(RankCount, ProviderCompanyId) 
			EXEC ('Select COUNT(ProviderCompanyId), ProviderCompanyId FROM tblPremiumRankByCarMakeModel Where Position = 5' + @strWhere + 'Group By ProviderCompanyId ')
			
			--Select * from tblTempCompanyRank
			
			UPDATE    PR
				SET   FifthCount = T.RankCount
			FROM #tblTemp5CompanyRank AS T INNER JOIN
			tblPremiumRankByCompanyTempReport AS PR ON T.ProviderCompanyId = PR.ProviderCompanyId
								  
			--Select * from tblPremiumRankByCompanyTempReport
			
			DROP TABLE #tblTemp5CompanyRank
		END
	Set @cPosition = @cPosition + 1
END

Select * from tblPremiumRankByCompanyTempReport
--Select * from tblCarInsuranceResult Where ScenarioId = 1 And ProviderCompanyId = 34 And CarMakeId = 201 And CarModelId = 4